clear all                 	/*clears whatever data are in memory*/
capture log close         	/*closes whatever log is open*/
set more off, permanently   /*disable annoying "more" response*/
set mem 50m


*-----------------------------------------------* .
*                 INPUTS                        * .
*-----------------------------------------------* .
local cbsa_analysisDS /Users/jess/documents/nccs/data/cbsa/dta/cbsa_analysisDS.dta

local ecn_cbsa /Users/jess/documents/nccs/data/ecn/dta/ecn_cbsa.dta

local qwi_cbsa /Users/jess/documents/nccs/data/qwi/dta/qwi_cbsa.dta

local fnd_cbsa /Users/jess/documents/nccs/data/fnd/dta/fnd_cbsa.dta

*-----------------------------------------------* .
*                 OUTPUTS                       * .
*-----------------------------------------------* .
local metro_index_2010_v2 /Users/jess/documents/nccs/data/cbsa/csv/metro_index_2010_v2.xls

/* local metro_index_2000 /Users/jess/documents/nccs/data/cbsa/csv/metro_index_2000.xls
local metro_index_1990 /Users/jess/documents/nccs/data/cbsa/csv/metro_index_1990.xls

local relig_table_2000 /Users/jess/documents/nccs/data/cbsa/csv/relig_table_2000.xls
local relig_table_2010 /Users/jess/documents/nccs/data/cbsa/csv/relig_table_2010.xls
*/

*-----------------------------------------------* .
*                TEMP FILES                     * .
*-----------------------------------------------* .


*-----------------------------------------------* .
*                VARIABLES                      * .
*-----------------------------------------------* .
local dv_vars exp_pcap n_org_pcap fnd_pcap fgp_pcap 

local med_vars_above exp_pcap n_org_pcap fnd_pcap fgp_pcap totphil_pcap indep_pcap totphil2_pcap ///
				     totemp_pcap pbemp_pcap prvemp_pcap prvemp2_pcap
		
local med_vars_below fprft_pct 

** LOAD ANALYSIS DATA **
use "`cbsa_analysisDS'", clear
describe

keep REGID REGNAME region yr `dv_vars' 
sort REGID yr
describe

	

** MERGE DATA SETS **
merge 1:1 REGID yr using "`ecn_cbsa'", generate(merge_flag)
describe

gen prvemp2_pcap=totemp_pcap

tab yr if merge_flag==1
keep REGID REGNAME yr region `dv_vars' fprft_pct prvemp2_pcap
describe

merge 1:1 REGID yr using "`qwi_cbsa'", generate(merge_flag)
tab yr if merge_flag==1
describe

keep REGID REGNAME yr region `dv_vars' fprft_pct prvemp_pcap pbemp_pcap prvemp2_pcap totemp_pcap
describe

label var fprft_pct "ECN: Percent of Private Employees in For-Profit per 100000 persons "
label var prvemp2_pcap "ECN: Total Private Employment per 100000 persons "

label var pbemp_pcap "QWI: Public Social Service Sector Employment per 100000 persons "
label var prvemp_pcap "QWI: Private Social Service Sector Employment per 100000 persons (Nonprofit, For-Profit)"
label var totemp_pcap "QWI: Total Social Service Sector Employment per 100000 persons (Public, Nonprofit, For-Profit)"
describe
tab yr

keep if yr==2010
merge 1:1 REGID using "`fnd_cbsa'", generate(merge_flag)
describe
keep REGID REGNAME yr region `dv_vars' fprft_pct prvemp_pcap pbemp_pcap prvemp2_pcap totemp_pcap indep_pcap
label var indep_pcap "FND: Independent Foundation Spending ($ per person)"
describe
summ indep_pcap

* CREATE COMBINED FOUNDATION SPENDING VARIABLE * 
gen totphil_pcap=fnd_pcap+ fgp_pcap

label var totphil_pcap "Combined Community Foundation and Federated Giving Program Spending ($ per person)"

gen totphil2_pcap=fnd_pcap+ fgp_pcap+ indep_pcap

label var totphil2_pcap "Combined Foundation Spending--COMM, FGP, INDEP ($ per person)"


** CREATE MEDIAN FLAGS: 2010 **
keep if yr==2010
drop if REGID=="46060" | REGID=="45300"

foreach i in `med_vars_above' {
egen med_`i' = median(`i')

egen z_`i'= std(`i')
replace z_`i'=. if `i'==.

gen `i'_flag = 99
replace `i'_flag=1 if `i'>med_`i'
replace `i'_flag=0 if `i'<=med_`i'

gsort -`i'
list REGNAME `i' med_`i' `i'_flag
}

foreach i in `med_vars_below' {
egen med_`i' = median(`i')

egen z_`i'= std(`i')
replace z_`i'=. if `i'==.

gen `i'_flag = 99
replace `i'_flag=1 if `i'<=med_`i'
replace `i'_flag=0 if `i'>med_`i'

gsort -`i'
list REGNAME `i' med_`i' `i'_flag
}

drop med_*
describe

* CREATE NORMED FOUNDATION SPENDING VARIABLE * 
gen ztotphil2_pcap=z_fnd_pcap + z_fgp_pcap + z_indep_pcap

label var ztotphil2_pcap "Normalized Foundation Spending--COMM, FGP, INDEP ($ per person)"

gsort -totphil2_pcap
list REGNAME ztotphil2_pcap totphil2_pcap

gsort -ztotphil2_pcap
list REGNAME ztotphil2_pcap totphil2_pcap

* Create Median flags for zotphil2_pcap *
egen med_ztotphil2_pcap = median(ztotphil2_pcap)

egen z_ztotphil2_pcap= std(ztotphil2_pcap)
replace z_ztotphil2_pcap=. if ztotphil2_pcap==.

gen ztotphil2_pcap_flag = 99
replace ztotphil2_pcap_flag=1 if ztotphil2_pcap>med_ztotphil2_pcap
replace ztotphil2_pcap_flag=0 if ztotphil2_pcap<=med_ztotphil2_pcap

gsort -ztotphil2_pcap
list REGNAME ztotphil2_pcap med_ztotphil2_pcap ztotphil2_pcap_flag



gen metro_index_totphil=exp_pcap_flag + totphil_pcap + /// 
				fprft_pct_flag + totemp_pcap_flag

gsort -metro_index -exp_pcap /*-exp_pcap_flag -fnd_ass_pcap_flag -fgp_ass_pcap -fprft_pct_flag -totemp_pcap_flag -exp_pcap*/

list REGNAME exp_pcap_flag  fnd_pcap_flag  fgp_pcap_flag totphil_pcap_flag fprft_pct_flag totemp_pcap_flag metro_index

label var exp_pcap_flag "Nonprofit Spending ($ per person)-- ABOVE MEDIAN"
label var n_org_pcap_flag "Number of nonprofit social service agencies-- ABOVE MEDIAN"
label var fnd_pcap_flag "Community Foundation Spending ($ per person)-- ABOVE MEDIAN"
label var fgp_pcap_flag "Federated Giving Program Spending ($ per person)-- ABOVE MEDIAN"
label var totphil_pcap_flag "Combined Community Foundation and Federated Giving Program Spending ($ per person)-- ABOVE MEDIAN"
label var indep_pcap_flag "Independent Foundation Spending ($ per person)-- ABOVE MEDIAN"
label var totphil2_pcap_flag "Combined Foundation Spending ($ per person)-- ABOVE MEDIAN"
label var ztotphil2_pcap_flag "Normalized Foundation Spending--COMM, FGP, INDEP ($ per person)--ABOVE MEDIAN"

label var fprft_pct_flag "Percent of Private Employees in For-Profit-- BELOW MEDIAN"
label var totemp_pcap_flag "Total Social Service Sector Employment per 100000-- ABOVE MEDIAN"
label var pbemp_pcap_flag "Public Social Service Sector Employment per 100000-- ABOVE MEDIAN"
label var prvemp2_pcap_flag "Private Social Service Sector Employment per 100000 (from ECN)-- ABOVE MEDIAN"
label var prvemp_pcap_flag "Private Social Service Sector Employment per 100000 (from QWI)-- ABOVE MEDIAN"

label var metro_index "Metro Index"

label var z_exp_pcap "Nonprofit Spending ($ per person)-- Z-SCORE"
label var z_n_org_pcap "Number of nonprofit social service agencies-- Z-SCORE"
label var z_fnd_pcap "Community Foundation Spending ($ per person)-- Z-SCORE"
label var z_fgp_pcap "Federated Giving Program Spending ($ per person)-- Z-SCORE"
label var z_totphil_pcap "Combined Community Foundation and Federated Giving Program Spending ($ per person)-- Z-SCORE"
label var z_indep_pcap "Independent Foundation Spending ($ per person)-- Z-SCORE"
label var z_totphil2_pcap "Combined Foundation Spending ($ per person)-- Z-SCORE"
label var z_ztotphil2_pcap "Normalized Foundation Spending--COMM, FGP, INDEP ($ per person)-- Z-SCORE"

label var z_fprft_pct "Percent of Private Employees in For-Profit-- Z-SCORE"
label var z_totemp_pcap "Total Social Service Sector Employment per 100000-- Z-SCORE"
label var z_pbemp_pcap "Public Social Service Sector Employment per 100000-- Z-SCORE"
label var z_prvemp2_pcap "Private Social Service Sector Employment (from ECN) per 100000-- Z-SCORE"
label var z_prvemp_pcap "Private Social Service Sector Employment (from QWI) per 100000-- Z-SCORE"

local all_vars exp_pcap n_org_pcap fnd_pcap fgp_pcap totphil_pcap indep_pcap totphil2_pcap ztotphil2_pcap ///
				fprft_pct totemp_pcap pbemp_pcap prvemp_pcap prvemp2_pcap

local all_flag_vars exp_pcap_flag n_org_pcap_flag fnd_pcap_flag fgp_pcap_flag totphil_pcap_flag indep_pcap_flag totphil2_pcap_flag ztotphil2_pcap_flag ///
				 fprft_pct_flag totemp_pcap_flag pbemp_pcap_flag prvemp_pcap_flag prvemp2_pcap_flag

local all_z_vars z_exp_pcap z_n_org_pcap z_fnd_pcap z_fgp_pcap z_totphil_pcap z_indep_pcap z_totphil2_pcap z_ztotphil2_pcap /// 
			z_fprft_pct z_totemp_pcap z_pbemp_pcap z_prvemp_pcap z_prvemp2_pcap

export excel REGID REGNAME region `all_vars'  `all_flag_vars' `all_z_vars' /// 
	 using "`metro_index_2010_v2'" ///
	, replace firstrow(varlabels)	
	

describe
